﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using Model;
using Dal;
namespace Execl导入导出.Controllers
{
    public class HomeController : Controller
    {
        StuDal dal = new StuDal();
        //
        // GET: /Home/
        public ActionResult Index()
        {
            List<Student> list = dal.Show();
            return View(list);
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public ActionResult Sc(HttpPostedFileBase file)
        {
            //获取绝对路径
            string filename=Server.MapPath("/Models/"+DateTime.Now.ToString("yyyyMMddhhmm")+Path.GetExtension(file.FileName));
            //连接Excel表格
            string excelstr = "Provider=Microsoft.ace.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 8.0;";
            //保存数据
            file.SaveAs(filename);

            #region
            
            OleDbConnection conn = new OleDbConnection(excelstr);
            conn.Open();
            OleDbDataAdapter dap = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            #endregion

            foreach (DataRow dt in ds.Tables[0].Rows)
            {
                Student stu = new Student();
                stu.ID = Convert.ToInt32(dt[0]);
                stu.Name = dt[1].ToString();
                stu.Sex = dt[2].ToString();
                stu.Age = Convert.ToInt32(dt[3]);

                dal.Add(stu);
            }

            List<Student> list = dal.Show();
            return View("Index",list);
        
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <returns></returns>
        public ActionResult DaoChu()
        {
            List<Student> list = dal.Show();
            string excelstr = "Provider=Microsoft.ace.OLEDB.12.0;Data Source='F:/s.xlsx';Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(excelstr);
            conn.Open();
            string sql = "create table [Sheet1$] ([编号] INT,[姓名] Text,[性别] Text,[年龄] INT)";
            OleDbCommand cmd = new OleDbCommand(sql,conn);
            cmd.ExecuteNonQuery();

            foreach (Student item in list)
            {
                string sqls = "insert into [Sheet1$] values('"+item.ID+"','"+item.Name+"','"+item.Sex+"','"+item.Age+"')";

                cmd = new OleDbCommand(sqls,conn);

                cmd.ExecuteNonQuery();
            }

            conn.Close();

            return View();

            
        }
	}
}